package it.univaq.disim.mwt.business.impl;

import it.univaq.disim.mwt.business.SegreteriaService;
import it.univaq.disim.mwt.business.TipologieSegreterieService;
import it.univaq.disim.mwt.business.model.TipologiaSegreteria;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class JDBCTipologiaSegreteriaService implements TipologieSegreterieService{
	
	TipologiaSegreteria tipologiaSegreteria = null;
	
	//FUNZIONA
	@Override
	public TipologiaSegreteria findSegreteriaById(Long id) {
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		String sql=" select * from tipologie_segreterie where id = ? ";
		
		try {
		
			connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setLong(1, id);
			preparedStatement.executeQuery();	
            resultSet = preparedStatement.getResultSet();
            resultSet.next();
            
			Long idSegreteria = resultSet.getLong("id");
			String tipologia = resultSet.getString("tipologia");
		    
			tipologiaSegreteria = new TipologiaSegreteria(idSegreteria,tipologia);

		    resultSet.close();
			preparedStatement.close();
			connection.close();
			
			return  tipologiaSegreteria;

		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		
		return null;
	}

	//FUNZIONA
	@Override
	public List<TipologiaSegreteria> findAllSegreterie() {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		List<TipologiaSegreteria> listTipoSegr = new ArrayList<TipologiaSegreteria>();
	
		String sql = "select * from tipologie_segreterie";
		
		try {
			
			connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.executeQuery();	
            resultSet = preparedStatement.getResultSet();
            
			while(resultSet.next()){
				

				Long idSegreteria = resultSet.getLong("id");
				String tipologia = resultSet.getString("tipologia");
			    
				tipologiaSegreteria = new TipologiaSegreteria(idSegreteria,tipologia);
				
				listTipoSegr.add(tipologiaSegreteria);
			}
						
			resultSet.close();
			preparedStatement.close();
			connection.close();
			
			return listTipoSegr;

			} catch (SQLException e) {
				
				e.printStackTrace();
			}

		return null;
	}

}